package by.grsu.crs.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import by.grsu.crs.dao.CarDao;
import by.grsu.crs.model.Car;
import by.grsu.crs.model.ICar;

//FIXME: all notes below are applicable for the each class in this package


public class CarDaoImpl implements CarDao {
	private static final String INSERT_NEW_CAR = "INSERT INTO cars(registration_number, model, description, photo_urls, available, price) VALUES (?, ?, ?, ?, ?, ?)";

	private static final String SELECT_ALL_CARS = "SELECT id, registration_number, model, description, photo_urls, available, price FROM cars";

	private static final String SELECT_CAR_BY_ID = "SELECT id, registration_number, model, description, photo_urls, available, price FROM cars WHERE id = ?";

	private static final String DELETE_CAR_BY_ID = "DELETE FROM cars WHERE id = ?;";

	private static final String UPDATE_CAR_BY_ID = "UPDATE cars SET registration_number = ?, model = ?, description = ?, photo_urls = ?, available = ?, price = ? WHERE id = ?";

	private final Connection dbConnection;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public CarDaoImpl(final Connection dbConnection) {
		this.dbConnection = dbConnection;
	}

	public boolean addCar(final ICar car) throws SQLException {
		try {
			// FIXME: why it field of the class? it should be simple local
			// variable I think
			preparedStatement = dbConnection.prepareStatement(INSERT_NEW_CAR);
			preparedStatement.setString(1, car.getRegistrationNumber());
			preparedStatement.setString(2, car.getModel());
			preparedStatement.setString(3, car.getDescription());
			preparedStatement.setString(4, car.getPhotoUrls().toString());
			preparedStatement.setBoolean(5, car.isAvailable());
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch (final Exception e) {
			// TODO: log this
			e.printStackTrace();
		} finally {
			// FIXME: it is copy/pasted code. all methods have similar parts. At
			// least you should extract method like closeStatement
			// (PreparedStatement statement)
			// Or see example below -it demonstrates another good approach
			preparedStatement.close();
		}

		return false;
	}

	public boolean addCarDZ(final ICar car) throws SQLException {
		final PreparedStatement statement = dbConnection.prepareStatement(INSERT_NEW_CAR);
		statement.setString(1, car.getRegistrationNumber());
		statement.setString(2, car.getModel());
		statement.setString(3, car.getDescription());
		statement.setString(4, car.getPhotoUrls().toString());
		statement.setBoolean(5, car.isAvailable());

		executeStatement(new StatementCallback<Boolean>(statement) {

			@Override
			public Boolean execute() throws SQLException {
				if (statement.executeUpdate() == 1) {
					return true;
				} else {
					return false;
				}
			}
		});

		return false;
	}

	public void executeStatement(final StatementCallback statementCallback) throws SQLException {
		try {
			statementCallback.execute();
		} catch (final Exception e) {

		} finally {
			statementCallback.getPreparedStatement().close();
		}

	}

	public List<Car> getAllCars() throws SQLException {
		final List<Car> cars = new ArrayList<Car>();

		try {
			preparedStatement = dbConnection.prepareStatement(SELECT_ALL_CARS);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				cars.add(new Car(resultSet.getInt("id"), resultSet.getString("registration_number"), resultSet.getString("model"), resultSet
						.getString("description"), Arrays.asList(resultSet.getString("photo_urls")), resultSet.getBoolean("available"), resultSet
						.getInt("price")));
			}
		} catch (final Exception e) {
			// TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}

		return cars;
	}

	public ICar getCarById(final int id) throws SQLException {
		ICar car = null;

		try {
			preparedStatement = dbConnection.prepareStatement(SELECT_CAR_BY_ID);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();

			resultSet.last();
			if (resultSet.getRow() != 0) {
				resultSet.first();

				final String registrationNumber = resultSet.getString("registration_number");
				final String model = resultSet.getString("model");
				final String description = resultSet.getString("description");
				final List<String> photoUrls = Arrays.asList(resultSet.getString("photo_urls"));
				final boolean available = resultSet.getBoolean("available");
				final int price = resultSet.getInt("price");

				car = new Car(id, registrationNumber, model, description, photoUrls, available, price);
			}
		} catch (final Exception e) {
			// TODO: log this
			e.printStackTrace();
		} finally {
			resultSet.close();
			preparedStatement.close();
		}

		return car;
	}

	public boolean deleteCarByID(final int id) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(DELETE_CAR_BY_ID);
			preparedStatement.setInt(1, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch (final Exception e) {
			// TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}

		return false;
	}

	public boolean updateCarById(final int id, final ICar car) throws SQLException {
		try {
			preparedStatement = dbConnection.prepareStatement(UPDATE_CAR_BY_ID);
			preparedStatement.setString(1, car.getRegistrationNumber());
			preparedStatement.setString(2, car.getModel());
			preparedStatement.setString(3, car.getDescription());
			preparedStatement.setString(4, car.getPhotoUrls().toString());
			preparedStatement.setBoolean(5, car.isAvailable());
			preparedStatement.setInt(6, car.getPrice());
			preparedStatement.setInt(7, id);
			if (preparedStatement.executeUpdate() == 1) {
				return true;
			}
		} catch (final Exception e) {
			// TODO: log this
			e.printStackTrace();
		} finally {
			preparedStatement.close();
		}

		return false;
	}

}
